# coding=utf-8
import datetime
import urllib

import pymysql
from lxml import etree

db = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='',
    db='gupiao',
    charset='utf8'
)

# db = pymysql.Connect(
#     host='apiccr.megowx.com',
#     port=3306,
#     user='gupiao',
#     passwd='Aitrip123',
#     db='gupiao',
#     charset='utf8'
# )
cursor = db.cursor()

sql_dk = "INSERT INTO `fa_stock_dfk` (`date`, `code`, `jgcy`, `zlr`, `clr`, `zc1`, `zc20`, `kpflag`) VALUES "

lastTime = ""
try:
    sql0 = "SELECT date FROM `fa_stock_dfk` WHERE 1 ORDER BY id DESC LIMIT 1 "
    result = cursor.execute(sql0)
    db.commit()
    if result:
        date = cursor.fetchone()
        lastTime = datetime.datetime(year=date[0].year, month=date[0].month, day=date[0].day).strftime('%Y-%m-%d')
except Exception as e:
    print(e)


def getHtml(url):
    html = urllib.request.urlopen(url).read()
    html = html.decode('gbk')
    return html


def querydata(code):
    global lastTime
    code0 = code[3:]

    a0 = []
    sql = "INSERT INTO `fa_stock_dfk` (`date`, `code`, `jgcy`, `zlr`, `clr`, `zc1`, `zc20`, `kpflag`) VALUES "

    try:
        Url = "http://data.eastmoney.com/stockcomment/" + code0 + ".html"
        html = getHtml(Url)
        data = etree.HTML(html)

        a0 = data.xpath('//*[@id="page"]/div[2]/div[2]/div[2]/div/div/div[5]/ul/li[1]/span[2]')
        # '机构参与度为36.85%，属于中度控盘'
        aa0 = a0[0].text
        k = aa0.find("%")
        jgcy = a0[0].text[6:k]
        k = aa0.find("于") + 1
        kpflag = a0[0].text[k:]
        a1 = data.xpath('//*[@id="page"]/div[2]/div[2]/div[2]/div/div/div[5]/ul/li[2]/span[2]/i[1]')
        # '-3751.59'
        zlr = a1[0].text
        a2 = data.xpath('//*[@id="page"]/div[2]/div[2]/div[2]/div/div/div[5]/ul/li[2]/span[2]/i[2]')
        # '5301.04'
        clr = a2[0].text
        a3 = data.xpath('//*[@id="page"]/div[2]/div[2]/div[2]/div/div/div[5]/ul/li[3]/span[2]')
        # '最近1日主力成本11.28元，最近20日主力成本11.64元'
        k = a3[0].text.find("元")
        zc1 = a3[0].text[8:k]
        k = a3[0].text.find("本", k) + 1
        zc20 = a3[0].text[k:-1]

        a4 = data.xpath('//*[@id="page"]/div[2]/div[2]/div[2]/div/div/div[5]/ul/li[1]/span[3]')
        # 数据日期：2019-05-17
        date = a4[0].text[5:]

        if lastTime == date:
            return 0

        str1 = "'" + str(date) + "'"
        str1 += ",'" + str(code) + "'"
        str1 += ",'" + str(jgcy) + "'"
        str1 += ",'" + str(zlr) + "'"
        str1 += ",'" + str(clr) + "'"
        str1 += ",'" + str(zc1) + "'"
        str1 += ",'" + str(zc20) + "'"
        str1 += ",'" + str(kpflag) + "'"

        sql += "(" + str1 + ")"
        sql = sql.replace('nan', 'null').replace('None', 'null').replace('none', 'null')
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(e)
        print(sql)
        print(code)
        print(a0)
        return False

    return True


code_list = []
try:
    sql = "SELECT code FROM `fa_stock_mk` where date = '2019-04-30' "
    result = cursor.execute(sql)

    if result:
        code_list = cursor.fetchall()

except Exception as e:
    print(e)

#### 打印结果集 ####
# sh.600000～sz.300773
num = 0
isFirst = 0

for i in range(0, len(code_list)):
    one = code_list[i]
    if isFirst == 0 and one[0] == "sh.600000":
        isFirst = 1

    if isFirst == 1:
        result = querydata(one[0])
        if result == 0:
            continue
        print('----Finish---' + str(num))
        num += 1
        if one[0] == "sz.300773":
            break
        # if num > 0:
        #     break

cursor.close()
db.close()

print('----OK---')
